﻿using System;
using System.Linq;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.ServiceModel.Activation;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Data;

namespace SSCCMembership.Web.Services.Members
{
    [ErrorHandlerBehavior]
    [ServiceContract(Namespace = "")]
    [AspNetCompatibilityRequirements(RequirementsMode = AspNetCompatibilityRequirementsMode.Allowed)]
    public class SlalomRankingService
    {
        const int SingleClassCT = 5;
        const int DoubleClassCT = 6;


        const string Query =
        "SELECT        SSCC_DAT_Member.FirstName, SSCC_DAT_Member.Surname, SSCC_DAT_Membership.ID, SSCC_DAT_Year.Year " +
        "FROM            SSCC_DAT_Member INNER JOIN " +
        "         SSCC_DAT_Membership ON SSCC_DAT_Member.ID = SSCC_DAT_Membership.Member INNER JOIN " +
        "         SSCC_DAT_Year ON SSCC_DAT_Membership.Year = SSCC_DAT_Year.ID " +
        "WHERE        (SSCC_DAT_Member.ID = @ID) AND (SSCC_DAT_Year.StartDate < @NOW) AND (SSCC_DAT_Year.EndDate > @NOW) ";


        [OperationContract]
        public SlalomRankingsData LoadRankings(int MemberID)
        {
            SecurityTester.UserCanEditMember(MemberID);
            var SRD = new SlalomRankingsData();
            using (var Q = SSCCDatabase.Query(Query, new Params()
            {
                {new SqlParameter("@ID", SqlDbType.Int), MemberID},
                {new SqlParameter("@NOW", SqlDbType.Date), DateTime.Now}
            }))
            {
                var R = Q.First();
                SRD.Year = (int)R["Year"];
                SRD.FullName = String.Format("{0} {1}", R["FirstName"], R["Surname"]);
                SRD.ID = (int)R["ID"];
            }
            using (var Q = SSCCDatabase.Query("SELECT * FROM SSCC_DAT_MemberDivisionHistory WHERE Membership=@MembershipID",
                new SqlParameter("@MembershipID",SqlDbType.Int),SRD.ID))
            {
                SRD.Rankings = Q.Select(Select).ToArray();
            }

            //SRD.Rankings = DC.SSCC_DAT_MemberDivisionHistory.Include("SSCC_DAT_Membership").Where((D) => D.SSCC_DAT_Membership.ID == Membership.ID).Select(Select).ToArray();


            return SRD;
        }
        public SlalomRankingData Select(SqlDataReader D)
        {
            var SRD = new SlalomRankingData()
            {
                BibNumber = D["BibNumber"].Nullable<int?>(),
                ClassName = D["ClassRC"].Nullable<int?>().GetRefCodeDescription(),
                ClassRC = D["ClassRC"].Nullable<int?>(),
                ClassWeight = D["ClassRC"].Nullable<int?>().GetRefCodeWeight(),
                DivisionRCL2 = D["DivisionRCL2"].Nullable<int?>(),
                ID = (int)D["ID"],
                PartnerName = D["PartnerName"].ToString(),
                HasPartner = (D["ClassRC"] == DBNull.Value || D["ClassRC"].Nullable<int?>().GetRefCodeCodeType() == DoubleClassCT),
                PartnerID = D["PartnerID"].Nullable<int?>()
            };
            return SRD;
        }
        //public SlalomRankingData Select(SSCC_DAT_MemberDivisionHistory D)
        //{
        //    var SRD = new SlalomRankingData()
        //        {
        //            BibNumber = D.BibNumber,
        //            ClassName = D.ClassRC.GetRefCodeDescription(),
        //            ClassRC = D.ClassRC,
        //            ClassWeight = D.ClassRC.GetRefCodeWeight(),
        //            DivisionRCL2 = D.DivisionRCL2,
        //            ID = D.ID,
        //            PartnerName = D.PartnerName,
        //            HasPartner = (D.ClassRC == null||D.ClassRC.GetRefCodeCodeType() == DoubleClassCT)
        //        };
        //    if (D.SSCC_DAT_Member != null)
        //    {
        //        SRD.PartnerID = D.SSCC_DAT_Member.ID;
        //    }
        //    return SRD;
        //}

        [OperationContract]
        public PotentialPartner[] GetAllPotentialPartners()
        {
            using (var Q = SSCCDatabase.Query("SELECT ID, FirstName, Surname FROM SSCC_DAT_Member"))
            {
                return Q.Select((R) => new PotentialPartner() { 
                    ID = (int)R["ID"], 
                    FullName = String.Format("{0} {1}", R["FirstName"], R["Surname"]) }).ToArray();
            }
        }

        //[OperationContract]
        //public void AddSlalomRanking(SlalomRankingData SRD, int MembershipID)
        //{
        //    SecurityTester.UserCanEditMembership(MembershipID);
        //    SSCC_DAT_Member Partner = null;
        //    if (SRD.PartnerID != null)
        //    {
        //        Partner = DC.SSCC_DAT_Member.Where((M) => M.ID == MembershipID).First();
        //    }
        //    DC.SSCC_DAT_Membership.Where((M) => M.ID == MembershipID)
        //        .First().SSCC_DAT_MemberDivisionHistory.Add(new SSCC_DAT_MemberDivisionHistory()
        //        {
        //            BibNumber = SRD.BibNumber,
        //            ClassRC = SRD.ClassRC,
        //            DivisionRCL2 = SRD.DivisionRCL2,
        //            PartnerName = SRD.PartnerName,
        //            SSCC_DAT_Member = Partner//SSCC_DAT_Member represents the partner
        //        });
        //    DC.SaveChanges();
        //}
        [OperationContract]
        public int AddSlalomRanking(int MembershipID)
        {
            SecurityTester.UserCanEditMembership(MembershipID);

            if (SSCCDatabase.ExecuteStatement("INSERT INTO SSCC_DAT_MemberDivisionHistory (Membership) Values (@MemberID)", 
                new SqlParameter("@MemberID", SqlDbType.Int), MembershipID) == 1)
                return SSCCDatabase.HighestID("SSCC_DAT_MemberDivisionHistory");

            throw new Exception("Failed to add slalom ranking");
        }

        [OperationContract]
        public void DeleteSlalomRankingData(int ID)
        {
            using (var R = SSCCDatabase.Query("SELECT Membership FROM SSCC_DAT_MemberDivisionHistory WHERE ID=@ID",
                new SqlParameter("@ID", SqlDbType.Int), ID).First())
            {
                SecurityTester.UserCanEditMembership((int)R["Membership"]);
            }
            SSCCDatabase.ExecuteStatement("DELETE FROM SSCC_DAT_MemberDivisionHistory WHERE ID=@ID", new SqlParameter("@ID", SqlDbType.Int), ID);
        }

        [OperationContract]
        public void UpdateSlalomRanking(int RankingID, string PropertyName, object value)
        {
            using (var R = SSCCDatabase.Query("SELECT Membership FROM SSCC_DAT_MemberDivisionHistory WHERE ID=@ID",
                new SqlParameter("@ID", SqlDbType.Int), RankingID).First())
            {
                SecurityTester.UserCanEditMembership((int)R["Membership"]);
            }
            SqlParameter P = new SqlParameter();
            switch (PropertyName)
            {
                case "PartnerID":
                case "BibNumber":
                case "ClassRC":
                case "DivisionRCL2":
                    P = new SqlParameter("@value", SqlDbType.Int);
                    break;
                case "PartnerName":
                    P = new SqlParameter("@value", SqlDbType.VarChar);
                    break;
                default:
                    return;
            }
            SSCCDatabase.ExecuteStatement("UPDATE SSCC_DAT_MemberDivisionHistory SET "+PropertyName +"=@value WHERE ID=@ID",
                new Params(){{P, value.ToDBNullable()},{new SqlParameter("@ID",SqlDbType.Int), RankingID}});
        }
    }

    public class SlalomRankingsData
    {
        public int Year { get; set; }
        public string FullName { get; set; }
        //Taken from MembershipID
        public int ID { get; set; }

        public SlalomRankingData[] Rankings { get; set; }
    }
    public class SlalomRankingData
    {
        public int ID { get; set; }
        public int? ClassRC { get; set; }
        public string ClassName { get; set; }
        public int ClassWeight { get; set; }
        public int? DivisionRCL2 { get; set; }
        public int DivisionWeight { get; set; }
        public int? BibNumber { get; set; }

        public int? PartnerID { get; set; }
        public string PartnerName { get; set; }

        public bool HasPartner { get; set; }
    }
    public class PotentialPartner
    {

        public int ID { get; set; }
        public string FullName { get; set; }
    }
}
